﻿using System;
using System.Collections.Generic;
using System.Linq;
using System.Text;
using System.Data;
using System.Data.SqlClient;
using System.Data.OleDb;
using System.Collections;
using System.Web;
using System.Configuration;

/// <summary>
/// Summary description for ConnectDB
/// </summary>
public abstract class StandardConnectDB
{
    private SqlConnection _conn;

    public StandardConnectDB()
    {
        this._conn = new SqlConnection("Server=" + ConnectDBConfig.ServerName + ";Database=" + ConnectDBConfig.DatabaseName + ";User ID=" + ConnectDBConfig.Uid + ";Password=" + ConnectDBConfig.Pwd + ";Trusted_Connection=False;");
    }

    public StandardConnectDB(string connectionString)
    {
        this._conn = new SqlConnection(connectionString);
    }

    public StandardConnectDB(string ServerName, string DatabaseName, string LoginName, string Password)
    {
        this._conn = new SqlConnection("Server=" + ServerName + ";Database=" + DatabaseName + ";User ID=" + LoginName + ";Password=" + Password + ";Trusted_Connection=False;");
    }

    public StandardConnectDB(string ServerName, string DatabaseName)
    {
        this._conn = new SqlConnection("Server=" + ServerName + ";Database=" + DatabaseName + ";Trusted_Connection=True;");
    }

    private void OpenConnection()
    {
        if (this._conn != null && this._conn.State == ConnectionState.Open)
        {
            this._conn.Close();
        }
        this._conn.Open();
    }

    private SqlCommand GetCommand(string sp_Name, Dictionary<string, object> paramList)
    {
        SqlCommand cmd = this._conn.CreateCommand();
        try
        {
            cmd.CommandText = sp_Name;
            cmd.CommandType = CommandType.StoredProcedure;
            foreach (KeyValuePair<string, Object> kvp in paramList)
            {
                cmd.Parameters.Add(new SqlParameter(kvp.Key, kvp.Value));
            }
        }
        catch (Exception ex)
        {
            throw ex;
        }
        return cmd;
    }

    private SqlCommand GetCommand()
    {
        return this._conn.CreateCommand();
    }

    public DataTable DTExecuteSelect(string query)
    {
        DataTable DT = new DataTable();
        try
        {
            SqlDataAdapter adapter = new SqlDataAdapter(query, this._conn);
            adapter.Fill(DT);
        }
        catch (Exception ex)
        {
            throw ex;
        }
        return DT;
    }


    public DataTable DTExecuteSelect(string sp_Name, Dictionary<string, object> paramList)
    {
        DataTable DT = new DataTable();
        try
        {
            SqlCommand cmd = this.GetCommand(sp_Name, paramList);
            SqlDataAdapter Adapter = new SqlDataAdapter(cmd);
            Adapter.Fill(DT);
        }
        catch (Exception ex)
        {
            throw ex;
        }
        return DT;
    }

    public object ExecuteScale(string query)
    {
        object obj = null;
        try
        {
            SqlCommand cmd = this.GetCommand();
            obj = cmd.ExecuteScalar();
        }
        catch (Exception ex)
        {
            throw ex;
        }
        return obj;
    }

    public SqlDataReader DRExecuteSelect(string query)
    {
        SqlDataReader reader = null;
        try
        {
            this.OpenConnection();
            SqlCommand cmd = this.GetCommand();
            cmd.CommandText = query;
            reader = cmd.ExecuteReader();
        }
        catch (Exception ex)
        {
            throw ex;
        }
        finally
        {
            //this._conn.Close();
        }
        
        return reader;
    }

    public SqlDataReader DRExecuteSelect(string sp_Name, Dictionary<string, object> paramList)
    {
        SqlDataReader reader = null;
        try
        {
            this.OpenConnection();
            SqlCommand cmd = this.GetCommand(sp_Name, paramList);
            reader = cmd.ExecuteReader();
        }
        catch (Exception ex)
        {
            throw ex;
        }
        finally
        {
            //this._conn.Close();
        }
        return reader;
    }

    public bool ExecuteUpdate(string query)
    {
        bool result = false;
        try
        {
            this.OpenConnection();
            SqlCommand cmd = this.GetCommand();
            cmd.CommandText = query;
            result = cmd.ExecuteNonQuery() > 0;
        }
        catch (Exception ex)
        {
            throw ex;
        }
        finally
        {
            this._conn.Close();
        }
        return result;
    }

    public bool ExecuteUpdate(string sp_Name, Dictionary<string, object> paramList)
    {
        bool result = false;
        try
        {
            this.OpenConnection();
            result = this.GetCommand(sp_Name, paramList).ExecuteNonQuery() > 0;
        }
        catch (Exception ex)
        {
            throw ex;
        }
        finally
        {
            this._conn.Close();
        }
        return result;
    }

    public void CloseConnection()
    {
        if (this._conn != null && this._conn.State == ConnectionState.Open)
        {
            this._conn.Close();
        }
    }

    //getter, setter..
    public SqlConnection Conn
    {
        get { return _conn; }
    }
}